<html>
<head>
<title>MSSQLSpatial - Microsoft SQL Server Spatial Database</title>
</head>

<body bgcolor="#ffffff">

<h1>MSSQLSpatial - Microsoft SQL Server Spatial Database</h1>

<p>This driver implements support for access to spatial tables in Microsoft SQL 
    Server 2008+ which contains the geometry and geography data types to repesent 
    the geometry columns.</p>

<h2>Connecting to a database</h2>

To connect to a MSSQL datasource, use a connection string specifying the database name,
with additional parameters as necessary. The connection strings must be prefixed 
    with &#39;<i>MSSQL:</i>&#39;.<br>
<blockquote><pre>
MSSQL:server=.\MSSQLSERVER2008;database=dbname;trusted_connection=yes</pre></blockquote>
In addition to the standard parameters of the 
    <a href="http://msdn.microsoft.com/en-us/library/ms130822.aspx">ODBC driver connection string</a> 
    format the following custom parameters can also be used in the following syntax:

<ul>
	
<li> <b>Tables=schema1.table1(geometry column1),schema2.table2(geometry column2)</b>: 
    By using this parameter you can specify the subset of the layers to be used by the driver. If this parameter is not set, the layers are retrieved from the geometry_columns metadata table. You can omit specifying the schema and the geometry column portions of the syntax. </li>
<li> <b>GeometryFormat=native|wkb|wkt</b>: 
    The desired format in which the geometries should be retrieved from the server. The default value is 'native' in this case the native SqlGeometry and SqlGeography serialization format is used. When using the 'wkb' or 'wkt' setting the geometry representation is converted to 'Well Known Binary' and 'Well Known Text' at the server. This conversion requires a significant overhead at the server and makes the feature access slower than using the native format.</li>

</ul>
    <p>The parameter names are not case sensitive in the connection strings.</p> 
    <p>Specifying the <b>
    Database</b> parameter is required by the driver in order to select the proper database.</p> 
    <p>The connection may contain the optional <b>
    Driver</b> parameter if a custom SQL server driver should be loaded (like FreeTDS). The default is <b>{SQL Server}</b></p>

<h2>SQL statements</h2>

<p>The MS SQL Spatial driver passes SQL statements directly to MS SQL by default,
rather than evaluating them internally when using the ExecuteSQL() call on the
OGRDataSource, or the -sql command option to ogr2ogr.  Attribute query
expressions are also passed directly through to MSSQL. 
It's also possible to request the OGR MSSQL driver to handle SQL commands 
with the <a href="/ogr/ogr_sql.html">OGR SQL</a> engine, by passing <strong>"OGRSQL"</strong> 
string to the ExecuteSQL() method, as the name of the SQL dialect.</p>

<p>The MSSQL driver in OGR supports the OGRLayer::StartTrasaction(), 
OGRLayer::CommitTransaction() and OGRLayer::RollbackTransaction()
calls in the normal SQL sense.</p>

<h2>Creation Issues</h2>

<p>This driver doesn't support creating new databases, you might want to use the <i>Microsoft SQL Server Client Tools</i> for this purpose, but it does allow creation of new layers within an
existing database.</P>

<h3>Layer Creation Options</h3>

<ul>
<li>
<b>GEOM_TYPE</b>: The GEOM_TYPE layer creation option can be set to 
one of "geometry" or "geography". If this option is not specified the default value is "geometry". 
    So as to create the geometry column with &quot;geography&quot; type, this parameter should 
    be set &quot;geography&quot;. In this case the layer must have a valid spatial referece of 
    one of the geography coordinate systems defined in the <b>
    sys.spatial_reference_systems</b> SQL Server metadata table. Projected 
    coordinate systems are not supported in this case.</li>
<li> <b>OVERWRITE</b>: This may be "YES" to force an existing layer of the
desired name to be destroyed before creating the requested layer.</li>
<li> <b>LAUNDER</b>: This may be "YES" to force new fields created on this
layer to have their field names "laundered" into a form more compatible with
MSSQL.  This converts to lower case and converts some special characters
like "-" and "#" to "_".  If "NO" exact names are preserved.  
The default value is "YES".  If enabled the table (layer) name will also be laundered.</li>
<li> <b>PRECISION</b>: This may be "YES" to force new fields created on this
layer to try and represent the width and precision information, if available
using numeric(width,precision) or char(width) types.  If "NO" then the types
float, int and varchar will be used instead.  The default is "YES".
<li> <b>DIM={2,3}</b>: Control the dimension of the layer.  Defaults to 3.</li>
<li> <b>GEOM_NAME</b>: Set the name of geometry column in the new table.  If 
omitted it defaults to <i>ogr_geometry</i>.</li>
<li> <b>SCHEMA</b>: Set name of schema for new table.
If this parameter is not supported the default schema "<i>dbo"</i> is used.</li>
<li> <b>SRID</b>: Set the spatial reference id of the new table explicitly.
The corresponding entry should already be added to the spatial_ref_sys metadata table. If this parameter is not set the SRID is derived from the authority code of source layer SRS.</li>
</ul>

<h3>Spatial Index Creation</h3>

<p>By default the MS SQL Spatial driver doesn't add spatial indexes to the tables during the layer creation. However you should create a spatial index by using the 
    following sql option:</p>

<blockquote><pre>create spatial index on schema.table</pre></blockquote>

<p>The spatial index can also be dropped by using the following syntax:</p>

<blockquote><pre>drop spatial index on schema.table</pre></blockquote>

<h2>Examples</h2>

<p>Creating a layer from an OGR data source</p>
<blockquote><pre>
ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes" "rivers.tab"</pre></blockquote>

<p>Connecting to a layer and dump the contents</p>
<blockquote><pre>
ogrinfo -al "MSSQL:server=.\MSSQLSERVER2008;database=geodb;tables=rivers;trusted_connection=yes"</pre></blockquote>

<p>Creating a spatial index</p>
<blockquote><pre>
ogrinfo -sql "create spatial index on rivers" "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes"</pre></blockquote>

</body>
</html>
